import json
import pymysql

teams = ['clippers', 'lakers', 'kings', 'warriors', 'suns', 'grizzlies', 'pelicans', 'mavericks', 'rockets', 'spurs',
            'jazz', 'nuggets', 'thunder', 'timberwolves', 'blazers', '76ers', 'celtics', 'knicks', 'nets', 'raptors',
            'hornets', 'hawks', 'heat', 'magic', 'wizards', 'bucks', 'bulls', 'cavaliers', 'pacers', 'pistons']
nbayears = range(1996,2018)
game_types = [1,2,4]


for nbayear in nbayears:
    for game_type in game_types:
        #对于无数据的json文件，使用异常来跳过打开
        try:
            with open(str(nbayear) + '年' + str(game_type) + '比赛数据.json') as f:
                contents = json.loads(f.read())
            datas = contents['teams']
        except:
            pass
        else:
            for data in datas:
                #1.选择profile中的球队基础信息
                profile = data['profile']
                pro = [str(nbayear),profile.get('city'), profile.get('displayAbbr'),profile.get('displayConference'),
                       profile.get('division'),profile.get('code'),str(game_type)]

                # 2.选择statAverage中的球队数据平均值
                statAverage = data['statAverage']
                stat = [str(statAverage.get('games')),str(statAverage.get('fgpct')),str(statAverage.get('tppct')),
                        str(statAverage.get('ftpct')),str(statAverage.get('offRebsPg')),str(statAverage.get('defRebsPg')),
                        str(statAverage.get('rebsPg')),str(statAverage.get('assistsPg')),str(statAverage.get('turnoversPg')),
                        str(statAverage.get('stealsPg')),str(statAverage.get('blocksPg')),str(statAverage.get('foulsPg')),
                        str(statAverage.get('pointsPg'))]
                #3.合并选取的  profile和 stataverage中的数据
                total = pro + stat
                print(total)
                # print(str(year) + '年' + str(game_type))
                conn = pymysql.connect(host="localhost", user="root",
                                       password="root", db="nba", port=3306, charset='utf8')
                # 使用cursor()方法获取操作游标
                cur = conn.cursor()
                # 编写sql语句
                sql = "INSERT INTO team_data(nbayear,city,displayAbbr,displayConference,division,teamCode,gameType,games,fgpct,tppct,ftpct,offRebsPg,defRebsPg,rebsPg,assistsPg,turnoversPg,stealsPg,blocksPg,foulsPg,pointsPg) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                effect = cur.execute(sql, total)
                conn.commit()
print('添加数据库成功！')
# 关闭游标
cur.close()
# 关闭连接
conn.close()
